<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Program_submissions_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function getlookup($type){
      $this->db->where('lookup_type',$type);
      return $this->db->get('app_lookup_values')->result();
    }

    public function save_program($data){
      $this->db->query("call app_programs_submissions_ins(?,?,?,?,?,?,?,?,?,?,?,@err,@msg)",$data);
      $que = $this->db->query("SELECT @err as err,@msg as msg");
      $msg; $err;
        foreach($que->result() as $keys){
          $msg = $keys->msg;
          $err = $keys->err;
        }
        $arr = array(
            'msg'   =>$msg,
            'err'   =>$err 
        );
      echo json_encode($arr);
    }

    public function update_program($data){
      $this->db->query("call app_programs_upd(?,?,?,?,?,?,?,?,?,?,@err,@msg)",$data);
      $que = $this->db->query("SELECT @err as err,@msg as msg");
      $msg; $err;
        foreach($que->result() as $keys){
          $msg = $keys->msg;
          $err = $keys->err;
        }
        $arr = array(
            'msg'   =>$msg,
            'err'   =>$err 
        );
      echo json_encode($arr);
    }

    public function insert_param($data){
      $this->db->query("call app_prog_params_ins(?,?,?,?,?,?,@err,@msg)",$data);
      $que = $this->db->query("SELECT @err as err,@msg as msg");
      $msg; $err;
        foreach($que->result() as $keys){
          $msg = $keys->msg;
          $err = $keys->err;
        }
        $arr = array(
            'msg'   =>$msg,
            'err'   =>$err 
        );
      echo json_encode($arr);
    }

    public function update_param($data){
      $this->db->query("call app_prog_params_upd(?,?,?,?,?,?,@err,@msg)",$data);
      $que = $this->db->query("SELECT @err as err,@msg as msg");
      $msg; $err;
        foreach($que->result() as $keys){
          $msg = $keys->msg;
          $err = $keys->err;
        }
        $arr = array(
            'msg'   =>$msg,
            'err'   =>$err 
        );
      echo json_encode($arr);
    }

    public function getprograms(){
      return $this->db->get('app_programs')->result();
    }

    public function getparams($id){
      return $this->db->query('SELECT * FROM app_prog_params WHERE prog_id = "'.$id.'" ORDER BY seq_no ASC ')->result();
    }

    public function getparamsbyid($id){
      return $this->db->query('SELECT * FROM app_prog_params WHERE prog_param_id = "'.$id.'" ')->result();
    }

    public function getdata2(){
      return $this->db->get('app_program_submissions_v')->result();
    }

    public function getscheds(){
      return $this->db->query("SELECT * FROM app_program_submissions_v WHERE (CASE DATE_FORMAT(run_date,'%Y-%m-%d %H:%i') WHEN '0000-00-00 00:00' THEN DATE_FORMAT(sched_date,'%Y-%m-%d %H:%i') ELSE DATE_FORMAT(last_run_date,'%Y-%m-%d %H:%i') END ) = DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i') AND `status` = 'PENDING'")->result();
    }

    public function updatestatus($id,$status,$error,$attachment){
      $arr = array();
      if(!empty($error)){
        array_push($arr, ' result = "'.$error.'" ');
      }
      if(!empty($status)){
        array_push($arr, ' status = "'.$status.'" '); 
      }
      if(!empty($attachment)){
        array_push($arr, ' output_file = "'.$attachment.'" '); 
      }      
      array_push($arr,' updated_date = NOW() ');
      $param = implode(',',$arr);
      if(!empty($id)){
         $this->db->query('UPDATE app_program_submissions SET '.$param.' WHERE run_id = '.$id);
      }
     
    }

    public function updatesuccess($id){
      $this->db->query('UPDATE app_program_submissions SET result = "NORMAL", status = "COMPLETED", updated_date = NOW()  WHERE run_id = '.$id);

      $this->db->where('run_id',$id);
      $this->db->limit(1);
      $q = $this->db->get('app_program_submissions')->result();
      foreach ($q as $w) {
        $data = array(
          $w->prog_id,
          $w->run_type,
          $w->param_values,
          $w->output_type,
          $w->output_option,
          $w->sched_type,
          $w->interval,
          $w->sched_date,
          $w->created_by,
          $w->email,
          'U'
        );
        $this->db->query("call app_programs_submissions_ins(?,?,?,?,?,?,?,?,?,?,?,@err,@msg)",$data);
      }
    }

    public function getData($fcode, $fname, $mname, $efn){
        $sql = "";
        $sql1 = ""; 
        $sql2 = "";
        $sql3 = "";
        $sql4 = "";
        $arr = array();
        if(!empty($fcode)){
          $sql1 = " program_code LIKE '". $fcode ."' ";
          array_push($arr,$sql1);
        }
        if(!empty($fname)){
          $sql2 = " program_name LIKE '". $fname ."' ";
          array_push($arr,$sql2);
        }
        if(!empty($mname)){
          $sql3 = " module_name LIKE '". $mname ."' ";
          array_push($arr,$sql3);
        }
        if(!empty($efn)){
          $sql4 = " exe_file_name LIKE '". $efn ."' ";
          array_push($arr,$sql4);
        }

        $sql = "WHERE ".implode(' AND ',$arr);
        $this->db->query('DROP TABLE IF EXISTS temp_table');
        $this->db->query('CREATE TEMPORARY TABLE temp_table AS (SELECT ap.program_code,
                                 ap.program_name,
                                 am.module_name,
                                 ap.exe_file_name,
                                 ap.`program_id`,
                                 ap.enable_flag
                          FROM app_programs ap
                            LEFT JOIN app_modules am ON am.`module_id` = ap.`module_id`)');
        $q = $this->db->query('SELECT * FROM temp_table '.$sql);
        return $q->result();
      }
}